<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;

class Tifapiao extends Commonwork
{

    public function shenhe($myId,$trackId,$workId){
        //echo $fpid;
        $workfloawde=Db::table("cg_workflow")->where(["id"=>$myId])->find();
        $fpid=$workfloawde["yw_id"];
        $ap=Db::table("apti_fp_apply")->where(["id"=>$fpid])->value("state");
        if($ap==2){
            $this->redirect('Apfapiao/xiugai', ['fpid' => $fpid,"from"=>2]);return;
        }
        $main=Db::table("apti_fp_apply")->alias("fp")
        ->join("employee ec","fp.cuser=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id")
        ->join("department d","d.id=de.department_id")
        ->join("job_employee je","ec.id=je.employee_id")
        ->join("job j","j.id=je.job_id")
        ->join("cg_jxs j1","fp.jid=j1.id","LEFT")
        ->where(["fp.id"=>$fpid,"de.index"=>1])
        ->field("fp.no,fp.ctime,fp.type,ec.em_name,d.de_name,j.job_name,j1.title as jxs,fp.remark,fp.fujian,fp.id,fp.state")
        ->find();
        $this->assign("main",$main);
        $fujian=[];
        if($main["fujian"])
            $fujian=Db::table("annex")->where("id in ({$main["fujian"]})")->select();

        $this->assign("fujian",$fujian);
        
            $apply=Db::table("cg_ti_hexiao")->alias("ahp")
            ->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
            ->join("cg_ti ap","ahd.ti_id=ap.id")
            ->join("cg_ti_list apd","ahd.list_id=apd.id")
            ->join("cg_jxs j","apd.jxs_id=j.id")
            ->join("gys g","apd.gys=g.id","LEFT")
            ->join("employee e","ahp.cuser=e.id")
            ->join("cg_feiyongkemu3 k3","apd.paymethod=k3.id","LEFT")
            ->join("apti_fp_detail fpd","fpd.hexiao_id=ahd.id")
            ->join("apti_fp_apply fp","fp.id=fpd.fp_id")
            ->group("fpd.id")
            ->where(["fp.id"=>$fpid])
            ->field("fpd.id,fpd.apply_no,fpd.apply_detail_no,fp.no,fpd.detail_no,fpd.hexiao_no,fpd.hexiao_detail_no,ap.id as apid,ahp.id as heid,e.em_name,g.gys_name,k3.title as kemu,j.title as jxs_name,ahd.weishuihexiaojine,ahd.shuilv,ahd.shuijin,ahd.hanshuihexiaojine,ahd.isVAT as dikou,fpd.fp_no,apd.payee,ap.type_id")
            ->select();
           
            //print_r($apply);
        
             $logs=$this->getlog(19,$fpid);
            //print_r($logs);
            $this->assign("logs",$logs);
           $this->assign("apply",$apply);
        return $this->fetch();
    }
	public function xiangqing($fpid){
		//echo $fpid;
		$main=Db::table("apti_fp_apply")->alias("fp")
		->join("employee ec","fp.cuser=ec.id","LEFT")
		->join("department_employee de","ec.id=de.employee_id")
		->join("department d","d.id=de.department_id")
		->join("job_employee je","ec.id=je.employee_id")
		->join("job j","j.id=je.job_id")
		->join("cg_jxs j1","fp.jid=j1.id","LEFT")
		->where(["fp.id"=>$fpid,"de.index"=>1])
		->field("fp.no,fp.ctime,fp.type,ec.em_name,d.de_name,j.job_name,j1.title as jxs,fp.remark,fp.fujian,fp.id,fp.state")
		->find();
		$this->assign("main",$main);
		$fujian=[];
		if($main["fujian"])
			$fujian=Db::table("annex")->where("id in ({$main["fujian"]})")->select();

		$this->assign("fujian",$fujian);
		
			$apply=Db::table("cg_ti_hexiao")->alias("ahp")
	    	->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
	    	->join("cg_ti ap","ahd.ti_id=ap.id")
	    	->join("cg_ti_list apd","ahd.list_id=apd.id")
	    	->join("cg_jxs j","apd.jxs_id=j.id")
	    	->join("gys g","apd.gys=g.id","LEFT")
	    	->join("employee e","ahp.cuser=e.id")
	        ->join("cg_feiyongkemu3 k3","apd.paymethod=k3.id","LEFT")
	        ->join("apti_fp_detail fpd","fpd.hexiao_id=ahd.id")
	        ->join("apti_fp_apply fp","fp.id=fpd.fp_id")
	        ->group("fpd.id")
	        ->where(["fp.id"=>$fpid])
	        ->field("fpd.id,fpd.apply_no,fpd.apply_detail_no,fp.no,fpd.detail_no,fpd.hexiao_no,fpd.hexiao_detail_no,ap.id as apid,ahp.id as heid,e.em_name,g.gys_name,k3.title as kemu,j.title as jxs_name,ahd.weishuihexiaojine,ahd.shuilv,ahd.shuijin,ahd.hanshuihexiaojine,ahd.isVAT as dikou,fpd.fp_no,apd.payee,ap.type_id")
	        ->select();
	       
	        //print_r($apply);
		 $logs=$this->getlog(19,$fpid);
            //print_r($logs);
            $this->assign("logs",$logs);
		
	       $this->assign("apply",$apply);
		return $this->fetch();
	}
	public function xgapply(){
		$request = Request::instance();
    	$data=$request->post();
    	//print_r($data);
    	$updata["id"]=$data["pfid"];
    	$updata["state"]=1;
    	$updata["remark"]=$data["remark"];
    	$updata["fujian"]=$data["attach"];
    	$updata["puser"]=Session::get("employee_id");
    	$updata["ptime"]=date("Y-m-d H:i:s");
    	Db::startTrans();
    	try{
    		$res1=Db::table("apti_fp_apply")->update($updata);
    		for($i=0;$i<count($data["fpdid"]);$i++){
    			$dedata["id"]=$data["fpdid"][$i];
    			$dedata["fp_no"]=$data["fp_no"][$i];
    			$dedata["puser"]=Session::get("employee_id");
    			$dedata["ptime"]=date("Y-m-d H:i:s");
    			Db::table("apti_fp_detail")->update($dedata);
    		}
    	}catch(\Exception $e){
        	Db::rollBack();
        	//return  $e->getMessage();
            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        }
        if($data["state"]==3){
            $jieguo=$this->gowork(19,$data["pfid"]);
                if($jieguo==json_encode(['state'=>1,'msg'=>'提交成功'])){
                    Db::commit();
                    return $jieguo;
                }else{
                    Db::rollBack();
                    return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                }
        }else{
            Db::commit();
            //return  $e->getMessage();
            return json_encode(["msg"=>"","state"=>1]);
        }
	}
		//修改
	
	public function xiugai($fpid,$from=1){
        $this->assign("from",$from);
		//echo $fpid;
		$main=Db::table("apti_fp_apply")->alias("fp")
		->join("employee ec","fp.cuser=ec.id","LEFT")
		->join("department_employee de","ec.id=de.employee_id")
		->join("department d","d.id=de.department_id")
		->join("job_employee je","ec.id=je.employee_id")
		->join("job j","j.id=je.job_id")
		->join("cg_jxs j1","fp.jid=j1.id","LEFT")
		->where(["fp.id"=>$fpid,"de.index"=>1])
		->field("fp.no,fp.ctime,fp.type,ec.em_name,d.de_name,j.job_name,j1.title as jxs,fp.remark,fp.fujian,fp.id,fp.state")
		->find();
		$this->assign("main",$main);
		$fujian=[];
		if($main["fujian"])
			$fujian=Db::table("annex")->where("id in ({$main["fujian"]})")->select();

		$this->assign("fujian",$fujian);
		
			$apply=Db::table("cg_ti_hexiao")->alias("ahp")
	    	->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
	    	->join("cg_ti ap","ahd.ti_id=ap.id")
	    	->join("cg_ti_list apd","ahd.list_id=apd.id")
	    	->join("cg_jxs j","apd.jxs_id=j.id")
	    	->join("gys g","apd.gys=g.id","LEFT")
	    	->join("employee e","ahp.cuser=e.id")
	        ->join("cg_feiyongkemu3 k3","apd.paymethod=k3.id","LEFT")
	        ->join("apti_fp_detail fpd","fpd.hexiao_id=ahd.id")
	        ->join("apti_fp_apply fp","fp.id=fpd.fp_id")
	        ->group("fpd.id")
	        ->where(["fp.id"=>$fpid])
	        ->field("fpd.id,fpd.apply_no,fpd.apply_detail_no,fp.no,fpd.detail_no,fpd.hexiao_no,fpd.hexiao_detail_no,ap.id as apid,ahp.id as heid,e.em_name,g.gys_name,k3.title as kemu,j.title as jxs_name,ahd.weishuihexiaojine,ahd.shuilv,ahd.shuijin,ahd.hanshuihexiaojine,ahd.isVAT as dikou,fpd.fp_no,apd.payee,ap.type_id")
	        ->select();
	       
	        //print_r($apply);
		if($main["state"]==2){
            $btn=$this->getbton(19,$fpid);
            $this->assign("btn",$btn);
            $logs=$this->getlog(19,$fpid);
            //print_r($logs);
            $this->assign("logs",$logs);
           }
		
	       $this->assign("apply",$apply);
		return $this->fetch();
	}
	//ti补票申请审核
	/*public function shenhe($id){
		$updata["id"]=$id;$updata["state"]=7;
		$apids=Db::table("apti_fp_detail")->where(["fp_id"=>$id])->field("hexiao_id,fp_no")->select();
		
		//print_r($arr);exit;
		
		try{
			Db::table("apti_fp_apply")->update($updata);
			for($i=0;$i<count($apids);$i++){
				Db::table("cg_ti_hexiao_list")->where("id={$apids[$i]["hexiao_id"]}")->update(["bu_state"=>2,"youwufapiao"=>1,"fapiaohao"=>$apids[$i]["fp_no"]]);
			}
			
		}catch(\Exception $e){
			return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
		}
		  return json_encode(["msg"=>"","state"=>1]);
	}*/
		//ti补票申请删除
	public function shanchuap($id){
		$updata["id"]=$id;$updata["is_deleted"]=1;
		$apids=Db::table("apti_fp_detail")->where(["fp_id"=>$id])->field("hexiao_id")->select();
		$arr=[];
		for($i=0;$i<count($apids);$i++){
			$arr[]=$apids[$i]["hexiao_id"];
		}
		//print_r($arr);exit;
		$str=implode(',',$arr);
		try{
			Db::table("apti_fp_apply")->update($updata);
			Db::table("cg_ti_hexiao_list")->where("id in ($str)")->update(["bu_state"=>0]);
		}catch(\Exception $e){
			return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
		}
		  return json_encode(["msg"=>"","state"=>1]);
	}
    public function zuofeichuli(){
        $request = Request::instance();$data = $request->post();
        $fid=19;$aid=$data["pfid"];
        $res=$this->goworker($fid,$aid,$data["nodeKey"],$data["selectId"],$data["selectName"]);
        if($res["operateStatus"]=="success"){
            $updata["id"]=$aid;$updata["state"]=8;
            $apids=Db::table("apti_fp_detail")->where(["fp_id"=>$aid])->field("hexiao_id")->select();
            $arr=[];
            for($i=0;$i<count($apids);$i++){
                $arr[]=$apids[$i]["hexiao_id"];
            }
            //print_r($arr);exit;
            $str=implode(',',$arr);
            try{
                Db::table("apti_fp_apply")->update($updata);
                Db::table("cg_ti_hexiao_list")->where("id in ($str)")->update(["bu_state"=>0]);
            }catch(\Exception $e){
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
              return json_encode(["msg"=>"","state"=>1]);
        }else{
            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        }
    }
    public function baocunchuli(){
        $request = Request::instance();
        $data=$request->post();
        //print_r($data);
        $updata["id"]=$data["pfid"];
        $updata["state"]=3;
        $updata["remark"]=$data["remark"];
        $updata["fujian"]=$data["attach"];
        $updata["puser"]=Session::get("employee_id");
        $updata["ptime"]=date("Y-m-d H:i:s");
        Db::startTrans();
        try{
            $res1=Db::table("apti_fp_apply")->update($updata);
            for($i=0;$i<count($data["fpdid"]);$i++){
                $dedata["id"]=$data["fpdid"][$i];
                $dedata["fp_no"]=$data["fp_no"][$i];
                $dedata["puser"]=Session::get("employee_id");
                $dedata["ptime"]=date("Y-m-d H:i:s");
                Db::table("apti_fp_detail")->update($dedata);
            }
        }catch(\Exception $e){
            Db::rollBack();
            //return  $e->getMessage();
            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        }
       $res=$this->goworker(19,$updata["id"],$data["nodeKey"],$data["selectId"],$data["selectName"]);
        if($res["operateStatus"]=="success"){
            Db::commit();
            return json_encode(["msg"=>"","state"=>1]);
        }else{
            Db::rollBack();
            //return  $e->getMessage();
            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        }
       
    }
		//提交工作流
	public function tijiao($id){
		$updata["id"]=$id;
		$updata["puser"]=Session::get("employee_id");
    	$updata["ptime"]=date("Y-m-d H:i:s");
        db::startTrans();
    	try{
    		$res1=Db::table("apti_fp_apply")->update($updata);
    	}catch(\Exception $e){
        	
            Db::rollBack();
                return json_encode(["msg"=>$e->getMessage(),"state"=>2]);
        }
        if($res1){
            try{
             $jieguo=$this->gowork(19,$id);
            }catch(\Exception $e){
                
                Db::rollBack();
                    return json_encode(["msg"=>$e->getMessage(),"state"=>2]);
            }
               
                if($jieguo==json_encode(['state'=>1,'msg'=>'提交成功'])){
                    Db::commit();
                    return $jieguo;
                }else{
                    Db::rollBack();
                    return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                }
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
	}
	 public function produceSN(){
        //生成编号
        $max = db('apti_fp_apply')->where('no','like',date('Ymd').'%')->max('no'); //编号递增开始位置基础

        if($max){
            $max = (double)$max;
            $insert_sn = $max+1;
        }else{
            $insert_sn = date('Ymd').'0001';
        }
        return $insert_sn;
    }
	//创建申请
	public function newapply(){
		$request = Request::instance();
    	$data=$request->post();
    	//print_r($data);exit;
    	//生成申请
    	$apdata["no"]=$this->produceSN();
    	$apdata["jid"]=$data["jidn"];
    	$apdata["cuser"]=Session::get("employee_id");
    	$apdata["ctime"]=date("Y-m-d H:i:s");
    	$apdata["puser"]=Session::get("employee_id");
    	$apdata["ptime"]=date("Y-m-d H:i:s");
    	$apdata["state"]=1;
    	$apdata["remark"]=$data["remark"];
    	$apdata["fujian"]=$data["attach"];
    	$apdata["type"]=1;
    	//print_r($apdata);exit;
    	//return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
    	
    	Db::startTrans();

    	try{
    		$res1=Db::table("apti_fp_apply")->insertGetId($apdata);
    		
	    		//验证生成明细
	    	$dedata=[];
	    	for($i=0;$i<count($data["heid"]);$i++){
	    		if(trim($data["fp_no"][$i])){
	    			$tmp["fp_no"]=trim($data["fp_no"][$i]);
	    		}else{
	    			Db::rollBack();
	    			return json_encode(["msg"=>"发票编号不能为空","state"=>2]);
	    		}
	    		if(trim($data["heid"][$i])){
	    			$tmp["hexiao_id"]=trim($data["heid"][$i]);

	    			$mxs=Db::table("cg_ti_hexiao")->alias("ahp")
	    			->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
	    			->join("cg_ti ap","ahd.ti_id=ap.id")
			    	->join("cg_ti_list apd","ahd.list_id=apd.id")
			    	->join("cg_jxs j","apd.jxs_id=j.id")
			    	->join("gys g","apd.gys=g.id","LEFT")
			    	->join("employee e","ahp.cuser=e.id")
			    	->join("cg_feiyongkemu3 f3","apd.feetype=f3.id","LEFT")
					->where("ahd.id={$tmp["hexiao_id"]}")
					->field("ap.sn as apno,apd.sn as apdno,apd.id as apdid,ahp.sn as ahpno,ahd.sn as ahdno")
					->find();
					$tmp["detail_no"]=$i+1;
					$tmp["apply_id"]=$mxs["apdid"];
					$tmp["apply_no"]=$mxs["apno"];
					$tmp["apply_detail_no"]=$mxs["apdno"];
					$tmp["hexiao_no"]=$mxs["ahpno"];
					$tmp["hexiao_detail_no"]=$mxs["ahdno"];
					$tmp["cuser"]=Session::get("employee_id");
	    			$tmp["ctime"]=date("Y-m-d H:i:s");
	    			$tmp["puser"]=Session::get("employee_id");
	    			$tmp["ptime"]=date("Y-m-d H:i:s");
	    			$tmp["fp_id"]=$res1;
	    			$dedata[$i]=$tmp;
	    		}else{
	    			Db::rollBack();
	    			return json_encode(["msg"=>"请正确提交","state"=>2]);
	    		}
	    	}
	    	$res2=Db::table("apti_fp_detail")->insertAll($dedata);
	    	$heids=implode(',',$data["heid"]);
	    	$res3=Db::Table("cg_ti_hexiao_list")->where("id in ($heids)")->update(["bu_state"=>1]);
    	}catch(\Exception $e){
        	Db::rollBack();
        	//return  $e->getMessage();
            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        }
        if($res1&&$res2&&$res3){
        	if($data["state"]==3){
            $jieguo=$this->gowork(19,$res1);
                if($jieguo==json_encode(['state'=>1,'msg'=>'提交成功'])){
                    Db::commit();
                    return $jieguo;
                }else{
                    Db::rollBack();
                    return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                }
        }else{
            Db::commit();
            //return  $e->getMessage();
            return json_encode(["msg"=>"","state"=>1]);
        }
        }else{
        	Db::rollBack();
            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        }
	}
	//补发票申请
	public function bfpapply(){
		$where="ahp.status in(7) and ahd.youwufapiao=1 and (ahd.fapiaohao is null or ahd.fapiaohao='') and ahd.bu_state=0 ";
		$request = Request::instance();
    	$data=$request->post();
    	$aid=0;
    	$jxs=0;
    	if($data){
    		$aid=$data["aid"];
    		$jxs=$data["jxs"];
    	}
    	$this->assign("aid",$aid);
    	$this->assign("jxs",$jxs);

    	if($aid){
    		$ap=Db::table("cg_ti")->where(["id"=>$aid])->find();
    		$this->assign("ap",$ap);
    		$where.="and ap.id=$aid ";
    	}
    	if($jxs){
    		$gy=Db::table("cg_jxs")->where(["id"=>$jxs])->find();
    		$this->assign("gy",$gy);
    		$where.="and j.id=$jxs ";
    	}
    	if($aid||$jxs){
    		$apply=Db::table("cg_ti_hexiao")->alias("ahp")
	    	->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
	    	->join("cg_ti ap","ahd.ti_id=ap.id")
	    	->join("cg_ti_list apd","ahd.list_id=apd.id")
	    	->join("cg_jxs j","apd.jxs_id=j.id")
	    	->join("gys g","apd.gys=g.id","LEFT")
	    	->join("employee e","ahp.cuser=e.id")
	    	->join("cg_feiyongkemu3 f3","apd.feetype=f3.id","LEFT")
	    	->where($where)
	    	->field("ahd.bu_state,ahp.sn as ahpno,ahd.sn as ahdno,ahp.id as ahpid,ap.sn as apno,apd.sn as apdno,ap.id as apid,j.sn as jxs_no,j.title as jxs_name,apd.payee,ahd.weishuihexiaojine,ahd.shuijin,ahd.isVAT,e.em_name,ahd.id as heid,g.gys_name,f3.title as kemu,ahd.shuilv,ahd.hanshuihexiaojine,j.id as jid,ap.type_id")
    		->select();
    		$this->assign("apply",$apply);
    	}
		return $this->fetch();
	}
	//Ti补票申请列表
	public function index2($page=1,$list=10){
		$states=Db::table("ti_state")->order("paixu")->select();
    	$this->assign("states",$states);

    	$where="fp.is_deleted=0 ";
    	$request = Request::instance();

    	$data=$request->post();
    	if($data){
    		if(trim($data["no"])){
    			$no=trim($data["no"]);
    			$where.="and fp.no='$no' ";
    		}
    		if(trim($data["jxs"])){
    			$jxs=trim($data["jxs"]);
    			$where.="and j.jxs='$jxs' ";
    		}
    		if($data["state"])
    			$where.="and fp.state={$data["state"]} ";
    		if(trim($data["start"]))
    			$where.="and fp.ctime>='{$data["start"]} 00:00:00' ";
    		if(trim($data["end"]))
    			$where.="and fp.ctime<='{$data["end"]} 23:59:59' ";
    	}else{
    		$data=["no"=>"","jxs"=>"","state"=>0,"start"=>"","end"=>""];
    	}
    		$sum=Db::Table("apti_fp_apply")->alias("fp")
    	->join("cg_jxs j","fp.jid=j.id")
    	->join("ti_state s","fp.state=s.id")
    	->join("employee e","fp.cuser=e.id")
    	->where($where)->count();
    	
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

    	$apply=Db::Table("apti_fp_apply")->alias("fp")
    	->join("cg_jxs j","fp.jid=j.id")
    	->join("ti_state s","fp.state=s.id")
    	->join("employee e","fp.cuser=e.id")
    	->where($where)->order("fp.ctime desc")->limit($start,$list)
    	->field("fp.id,fp.no,j.title as jxs,s.title as states,e.em_name,fp.ctime,s.id as state")->select();
        for($i=0;$i<count($apply);$i++){
            //$apply[$i]["ctime"]=date("Y-m-d",$apply[$i]["ctime"]);
            $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>19])->value("id");
        }
    	$this->assign("apply",$apply);
    	$this->assign("data",$data);
    	$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		return $this->fetch();
		return $this->fetch();
	}
	//需补发票
	public function index(){
		$where="ahp.status in(7) and ahd.youwufapiao=1 and (ahd.fapiaohao is null or ahd.fapiaohao='') and ahd.bu_state<2 ";
		$request = Request::instance();
    	$data=$request->post();
    	if($data){
    		if(trim($data["jsn"])){
    			$jsn=trim($data["jsn"]);
    			$where.="and j.sn='{$jsn}' ";
    		}
    		if(trim($data["jname"])){
    			$jname=trim($data["jname"]);
    			$where.="and j.title='{$jname}' ";
    		}
    		if(trim($data["he_no"])){
    			$he_no=trim($data["he_no"]);
    			$where.="and ahp.sn='{$he_no}' ";
    		}
    		if(trim($data["ap_no"])){
    			$ap_no=trim($data["ap_no"]);
    			$where.="and ap.sn='{$ap_no}' ";
    		}
    	}else{
    		$data=["jsn"=>"","jname"=>"","he_no"=>"","ap_no"=>""];
	    }
	    $this->assign("data",$data);
    	$apply=Db::table("cg_ti_hexiao")->alias("ahp")
    	->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
    	->join("cg_ti ap","ahd.ti_id=ap.id")
    	->join("cg_ti_list apd","ahd.list_id=apd.id")
    	->join("cg_jxs j","apd.jxs_id=j.id")
    	->join("gys g","apd.gys=g.id","LEFT")
    	->join("employee e","ahp.cuser=e.id")
    	->where($where)
    	->field("ahd.bu_state,ahp.sn as ahpno,ahd.sn as ahdno,ahp.id as ahpid,ap.sn as apno,apd.sn as apdno,ap.id as apid,j.sn as jxs_no,j.title as jxs_name,apd.payee,g.gys_name,ahd.weishuihexiaojine,ahd.shuijin,ahd.isVAT,e.em_name,ap.type_id")
    	->select();
    	//echo Db::table("cg_ti")->getLastSql();
    	//print_r($apply);
    	$this->assign("apply",$apply);
    	$zongjine=0;$shuijin=0;
    	for($i=0;$i<count($apply);$i++){
    		$zongjine+=$apply[$i]["weishuihexiaojine"];
    		$shuijin+=$apply[$i]["shuijin"];
    	}
    	$this->assign("zongjine",$zongjine);
    	$this->assign("shuijin",$shuijin);
		return $this->fetch();
	}
	//导出
	public function daochu(){
			$where="ahp.status in(7) and ahd.youwufapiao=1 and (ahd.fapiaohao is null or ahd.fapiaohao='') and ahd.bu_state<2 ";
		$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
    	$apply=Db::table("cg_ti_hexiao")->alias("ahp")
    	->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
    	->join("cg_ti ap","ahd.ti_id=ap.id")
    	->join("cg_ti_list apd","ahd.list_id=apd.id")
    	->join("cg_jxs j","apd.jxs_id=j.id")
    	->join("gys g","apd.gys=g.id","LEFT")
    	->join("employee e","ahp.cuser=e.id")
    	->where($where)
    	->field("ahd.bu_state,ahp.sn as ahpno,ahd.sn as ahdno,ahp.id as ahpid,ap.sn as apno,apd.sn as apdno,ap.id as apid,j.sn as jxs_no,j.title as jxs_name,apd.payee,g.gys_name,ahd.weishuihexiaojine,ahd.shuijin,ahd.isVAT,e.em_name")
    	->select();
    	//print_r($apply);
    
    	$zongjine=0;$shuijin=0;
    	for($i=0;$i<count($apply);$i++){
    		$zongjine+=$apply[$i]["weishuihexiaojine"];
    		$shuijin+=$apply[$i]["shuijin"];
    	}
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("Ti需补发票清单");


           $objPHPExcel->getActiveSheet()->mergeCells('A1:K1');
	        $objPHPExcel->getActiveSheet()->setCellValue('A1', "Ti需补发票清单");
	              //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //加粗
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

	            #################表头信息
	        $objPHPExcel->getActiveSheet()->mergeCells('A2:K2');
	        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
	                //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //加粗
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);

	         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
	         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	        $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	          
	         $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
	         $objPHPExcel->getActiveSheet()->setCellValue('B3',"是否补发票中");
	         $objPHPExcel->getActiveSheet()->setCellValue('C3',"核销明细编号");
	         $objPHPExcel->getActiveSheet()->setCellValue('D3',"原活动明细编号");
	         $objPHPExcel->getActiveSheet()->setCellValue('E3',"经销商编号");
	         $objPHPExcel->getActiveSheet()->setCellValue('F3',"经销商名称");
	         $objPHPExcel->getActiveSheet()->setCellValue('G3',"收款方");
	         $objPHPExcel->getActiveSheet()->setCellValue('H3',"支付未税金额");
	         $objPHPExcel->getActiveSheet()->setCellValue('I3',"支付税金");
	         $objPHPExcel->getActiveSheet()->setCellValue('J3',"是否增值税");
	         $objPHPExcel->getActiveSheet()->setCellValue('K3',"核销申请人");
	         $h=3;
	         for($i=0;$i<count($apply);$i++){
				$h++;
				$bh=$i+1;

						$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
								  //水平剧中
				        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':J'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				        //垂直剧中
				        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':J'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
				        $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$bh);
				        if($apply[$i]["bu_state"])
				        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,"是");
				        else
				        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,"否");
				        $objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$apply[$i]["ahpno"].'_'.$apply[$i]["ahdno"]);
				        $objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$apply[$i]["apno"].'_'.$apply[$i]["apdno"]);
				        $objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$apply[$i]["jxs_no"]);
				        $objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$apply[$i]["jxs_name"]);
				        if($apply[$i]["payee"])
				        	$objPHPExcel->getActiveSheet()->setCellValue('G'.$h,"(供应商)".$apply[$i]["gys_name"]);
				        else
				        	$objPHPExcel->getActiveSheet()->setCellValue('G'.$h,"(经销商)".$apply[$i]["jxs_name"]);
				        $objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$apply[$i]["weishuihexiaojine"]);
				        $objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$apply[$i]["shuijin"]);
				         if($apply[$i]["isVAT"])
				        	$objPHPExcel->getActiveSheet()->setCellValue('J'.$h,"是");
				        else
				        	$objPHPExcel->getActiveSheet()->setCellValue('J'.$h,"否");
				        $objPHPExcel->getActiveSheet()->setCellValue('K'.$h,$apply[$i]["em_name"]);
	         }
	         $h++;
	         $objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
								  //水平剧中
				        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':K'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				        //垂直剧中
				        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':K'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

			 $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':K'.$h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	        		$objPHPExcel->getActiveSheet()->getStyle('A'.$h.':K'.$h)->getFill()->getStartColor()->setARGB('00FFCC99');

	        		$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,"总计");  $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:F{$h}");
	        		$objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$zongjine);$objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$shuijin);
	        		 $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

	        		  $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:K'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪

    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
     $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
      $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
       $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
         $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
          $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
             $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);

             $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=Ti需补发票清单.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	public function getjxs($id){
		$data = Db::table('cg_jxs')->where(["id"=>$id])->field('sn,title,id')->find();
		if($data)
			return "{$data["title"]}({$data["sn"]})";
		else
			return 0;
	}
		 //suggest查找经销商
	
    public function jxsjson(){
        $data = Db::table('cg_jxs')->field('title,id')->select();

     
        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
     public function jxsnojson(){
        $data = Db::table('cg_jxs')->field('sn as title,id')->select();

     
        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
     public function apjson(){
    	$where="ap.status in(7,9) and ap.status2 is null ";
        $data =Db::table("cg_ti")->alias("ap")
		->where($where)
		->field("ap.sn as title,ap.id")->select();

     
        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
}